package com.zg.datapush.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.zg.common.core.dao.system.SysDatasource;
import com.zg.common.core.dao.system.SysDatasourceDao;
import com.zg.common.dds.DataSourceUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * 表结构查询服务
 * @author zg
 */
@Service
@RequiredArgsConstructor
@Slf4j
public class TableStructureService {

    private final SysDatasourceDao sysDatasourceDao;
    private final DataSourceUtil dataSourceUtil;
    private final JdbcTemplate jdbcTemplate;

    /**
     * 获取表的字段信息
     */
    public List<Map<String, Object>> getTableColumns(String datasourceCode, String tableName) {
        SysDatasource datasource = sysDatasourceDao.getOne(
            new LambdaQueryWrapper<SysDatasource>()
                .eq(SysDatasource::getCode, datasourceCode)
        );

        if (datasource == null) {
            throw new RuntimeException("数据源不存在: " + datasourceCode);
        }

        // 使用DataSourceUtil切换到指定数据源
        dataSourceUtil.change(datasourceCode);

        try {
            // 根据数据库类型构建不同的查询SQL
            String sql = buildColumnQuerySql(datasource, tableName);

            return jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            log.error("获取表字段信息失败: datasource={}, table={}", datasourceCode, tableName, e);
            throw new RuntimeException("获取表字段信息失败: " + e.getMessage());
        } finally {
            // 清理数据源切换
            dataSourceUtil.clear();
        }
    }

    /**
     * 获取数据库中的所有表
     */
    public List<String> getTables(String datasourceCode) {
        SysDatasource datasource = sysDatasourceDao.getOne(
            new LambdaQueryWrapper<SysDatasource>()
                .eq(SysDatasource::getCode, datasourceCode)
        );

        if (datasource == null) {
            throw new RuntimeException("数据源不存在: " + datasourceCode);
        }

        // 使用DataSourceUtil切换到指定数据源
        dataSourceUtil.change(datasourceCode);

        try {
            // 根据数据库类型构建不同的查询SQL
            String sql = buildTableQuerySql(datasource);

            return jdbcTemplate.queryForList(sql, String.class);
        } catch (Exception e) {
            log.error("获取数据库表列表失败: datasource={}", datasourceCode, e);
            throw new RuntimeException("获取数据库表列表失败: " + e.getMessage());
        } finally {
            // 清理数据源切换
            dataSourceUtil.clear();
        }
    }



    /**
     * 根据数据库类型构建字段查询SQL
     */
    private String buildColumnQuerySql(SysDatasource datasource, String tableName) {
        String driverClass = datasource.getDriverClassName().toLowerCase();

        if (driverClass.contains("mysql")) {
            return "SELECT COLUMN_NAME as columnName, DATA_TYPE as dataType, " +
                   "IS_NULLABLE as isNullable, COLUMN_DEFAULT as columnDefault, " +
                   "COLUMN_COMMENT as columnComment, CHARACTER_MAXIMUM_LENGTH as maxLength " +
                   "FROM INFORMATION_SCHEMA.COLUMNS " +
                   "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" + tableName + "' " +
                   "ORDER BY ORDINAL_POSITION";

        } else if (driverClass.contains("postgresql")) {
            return "SELECT column_name as columnName, data_type as dataType, " +
                   "is_nullable as isNullable, column_default as columnDefault, " +
                   "'' as columnComment, character_maximum_length as maxLength " +
                   "FROM information_schema.columns " +
                   "WHERE table_schema = 'public' AND table_name = '" + tableName + "' " +
                   "ORDER BY ordinal_position";

        } else if (driverClass.contains("oracle")) {
            return "SELECT COLUMN_NAME as columnName, DATA_TYPE as dataType, " +
                   "NULLABLE as isNullable, DATA_DEFAULT as columnDefault, " +
                   "'' as columnComment, DATA_LENGTH as maxLength " +
                   "FROM USER_TAB_COLUMNS " +
                   "WHERE TABLE_NAME = '" + tableName.toUpperCase() + "' " +
                   "ORDER BY COLUMN_ID";

        } else if (driverClass.contains("sqlserver")) {
            return "SELECT COLUMN_NAME as columnName, DATA_TYPE as dataType, " +
                   "IS_NULLABLE as isNullable, COLUMN_DEFAULT as columnDefault, " +
                   "'' as columnComment, CHARACTER_MAXIMUM_LENGTH as maxLength " +
                   "FROM INFORMATION_SCHEMA.COLUMNS " +
                   "WHERE TABLE_NAME = '" + tableName + "' " +
                   "ORDER BY ORDINAL_POSITION";

        } else {
            // 默认使用MySQL语法
            return "SELECT COLUMN_NAME as columnName, DATA_TYPE as dataType, " +
                   "IS_NULLABLE as isNullable, COLUMN_DEFAULT as columnDefault, " +
                   "COLUMN_COMMENT as columnComment, CHARACTER_MAXIMUM_LENGTH as maxLength " +
                   "FROM INFORMATION_SCHEMA.COLUMNS " +
                   "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" + tableName + "' " +
                   "ORDER BY ORDINAL_POSITION";
        }
    }

    /**
     * 根据数据库类型构建表查询SQL
     */
    private String buildTableQuerySql(SysDatasource datasource) {
        String driverClass = datasource.getDriverClassName().toLowerCase();

        if (driverClass.contains("mysql")) {
            return "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES " +
                   "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE' " +
                   "ORDER BY TABLE_NAME";

        } else if (driverClass.contains("postgresql")) {
            return "SELECT table_name FROM information_schema.tables " +
                   "WHERE table_schema = 'public' AND table_type = 'BASE TABLE' " +
                   "ORDER BY table_name";

        } else if (driverClass.contains("oracle")) {
            return "SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME";

        } else if (driverClass.contains("sqlserver")) {
            return "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES " +
                   "WHERE TABLE_TYPE = 'BASE TABLE' " +
                   "ORDER BY TABLE_NAME";

        } else {
            // 默认使用MySQL语法
            return "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES " +
                   "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE' " +
                   "ORDER BY TABLE_NAME";
        }
    }
}
